import os
import platform

from PySide.QtCore import *
from PySide.QtSql import *

class Database(object):
    
    DB_NAME = "todo"
    DB_FILE_NAME = DB_NAME + ".db"
    SETTINGS_FOLDER_NAME = "." + DB_NAME
    
    def __init__(self):
        if platform.system() == "Linux":
            self.home_dir = os.environ['HOME']
        else:
            self.home_dir = os.environ['APPDATA']
        self.tables_exist = True
        self.db_paths()
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(self.database_file)
        db.open()
        if not self.tables_exist:
            self.create_tables()
            
    def create_folder(self):
        # make the directory
        try:
            os.mkdir(os.path.join(self.home_dir, self.SETTINGS_FOLDER_NAME))
        except OSError:
            # directory already exists
            pass
        
    def create_tables(self):
        # create tables etc
        query = QSqlQuery()
        query.exec_("create table tasks(id INTEGER PRIMARY KEY, date TEXT, task TEXT, category INTEGER)")
        query.exec_("create table categories(id INTEGER PRIMARY KEY, name TEXT, colour INT)")
        query.exec_("create table settings(id INTEGER PRIMARY KEY, name TEXT, value TEXT)")
        self.test_data()
        
    def test_data(self):
        query = QSqlQuery()
        query.exec_("insert into tasks values (NULL, '12/07/2011', 'test task 1', 1)")
        query.exec_("insert into tasks values (NULL, '13/07/2011', 'test task 2', 1)")
        query.exec_("insert into tasks values (NULL, '14/07/2011', 'test task 3', 1)")
    
    def db_paths(self):
        self.database_path = os.path.join(self.home_dir, self.SETTINGS_FOLDER_NAME)
        
        if not os.path.exists(self.database_path):
            self.create_folder()
        
        self.database_file = os.path.join(self.database_path, self.DB_FILE_NAME)
        
        if not os.path.exists(self.database_file):
            self.tables_exist = False